﻿using System.Data.SQLite;
using DVDTek_Data_Object.Collection;
using DVDTek_Data_Object.DataObject;

namespace DVDTek_Data_Object.Accessor
{
    internal class TypeAccessor
    {
        #region Constants
        internal const string TABLE_NAME = "Type";
        internal const string FN_PRIMARY_KEY = "id";
        private const string FN_ID = "id";
        private const string FN_TYPE = "type";
        #endregion

        #region Members
        private static TypeAccessor typeAccessor;
        internal static TypeAccessor Instance
        {
            get
            {
                if (typeAccessor == null)
                    typeAccessor = new TypeAccessor();

                return typeAccessor;
            }
        }

        internal string CreateTableQuery
        {
            get
            {
                return string.Concat(
                    "CREATE TABLE ", TABLE_NAME, " (",
                    FN_ID, " integer PRIMARY KEY AUTOINCREMENT NOT NULL, ",
                    FN_TYPE, " varchar(50) NOT NULL, ",
                    "CONSTRAINT un_", FN_TYPE,
                    " UNIQUE(", FN_TYPE, "));");
            }
        }
        #endregion

        #region Constructors
        private TypeAccessor()
        {
        }
        #endregion

        #region Insert / Update / Delete
        internal int Insert(string type)
        {
            string query = string.Concat(
                "INSERT INTO ", TABLE_NAME,
                " (", FN_ID, ",", FN_TYPE, ")",
                " VALUES ",
                "('', '", Common.QuoteEscape(type), "');",
                "SELECT last_insert_rowid();");

            return (int)Common.ExecuteScalarQuery(query);
        }

        internal void Update(int id, string type)
        {
            string query = string.Concat(
                "UPDATE ", TABLE_NAME, " SET ",
                FN_TYPE, "='", Common.QuoteEscape(type), "'",
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }

        internal void Delete(int id)
        {
            string query = string.Concat(
                "DELETE FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            Common.ExecuteNonQuery(query);
        }

        internal void Delete(string type)
        {
            string query = string.Concat(
                "DELETE FROM ", TABLE_NAME,
                " WHERE ", FN_TYPE, "='", Common.QuoteEscape(type), "';");

            Common.ExecuteNonQuery(query);
        }
        #endregion

        #region Select
        internal TypeDataObject Select(int id)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME,
                " WHERE ", FN_ID, "='", id, "';");

            SQLiteDataReader reader = Common.ExecuteQuery(query);

            if (reader.Read())
                return new TypeDataObject(reader.GetInt32(0), reader.GetString(1));
            else
                return null;
        }

        internal TypeDataObject Select(string type)
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME,
                " WHERE ", FN_TYPE, "='", Common.QuoteEscape(type), "';");

            SQLiteDataReader reader = Common.ExecuteQuery(query);

            if (reader.Read())
                return new TypeDataObject(reader.GetInt32(0), reader.GetString(1));
            else
                return null;
        }

        internal TypeCollection SelectAll()
        {
            string query = string.Concat(
                "SELECT * FROM ", TABLE_NAME, ";");

            SQLiteDataReader reader = Common.ExecuteQuery(query);
            TypeCollection types = new TypeCollection();

            while (reader.Read())
                types.Add(new TypeDataObject(reader.GetInt32(0), reader.GetString(1)));

            return types;
        }
        #endregion

        internal bool Exist(string type)
        {
            return Select(type) != null;
        }
    }
}
